package tsing.javase.dailyDemo;

import tsing.good_simple.goodutil.WriteStringUtil;

import java.util.ArrayList;
import java.util.List;

/**
 * @Date 2024/7/17 16:42
 * @Author Tsing
 */
public class SQLDemo {

    public static void main(String[] args) throws Exception {

        // 迁移SQL生成
        extracted();

/*        String excelPath ="C:\\Users\\23083\\Desktop\\Excel\\部门对应供应商ID.xlsx";
        List<SingleIdBean> ids = ExcelUtil.poiReadExcelMethod(SingleIdBean.class, excelPath, "id");

        System.out.println(JSON.toJSONString(ids));*/


    }

    private static void extracted() {
        List<String> list = new ArrayList<>();
        list.add("8de4b5d478724ccb82ff0544b7c8002b");// 测试供应商
   /*     String excelPath ="C:\\Users\\23083\\Desktop\\Excel\\全供应商.xlsx";
        ExcelUtil.poiReadExcelMethod(list,excelPath);*/
        System.out.println(list.size());

        List<String> result = new ArrayList<>();


        // 通用类型
        String s2 = "INSERT INTO tb_ecology_plateform_equity_apply_manage (equity_id,equity_name,type_id,logo_begin_time,logo_end_time,online_type,equity_brand_name,equity_brand_code,equity_description,market_price,market_price_notax,\n" +
                "supplier_price,supplier_price_notax,tax_rate,charge_type,vipppl_type,equity_channel_desc,exchange_path,use_desc,supplier_product_code,check_state,check_comments,supplier_id,is_show,insert_time,update_time,dept_id,del)\n" +
                "SELECT a.id,a.vippool_cont,c.interests_type,a.start_time,a.end_time,0,d.brand_name,c.brand,a.use_type_des,c.market_prices,c.market_prices/1.06,a.supplier_price,a.supplier_price_notax,\n" +
                "6.00,a.recharge_type,a.vippool_type,a.remark,a.exchange_path,a.use_type_des,a.product_code,0,'审核通过',a.supplier_id,0,now(),now(),b.dept_id,0\tFROM\n" +
                "tb_vipplate_interests_pool a LEFT JOIN tb_dept_supplier b ON a.supplier_id = b.supplier_id  left join tb_vipplate_interests c on a.interests_id = c.id \n" +
                "left join tb_vipplate_brand_conf d on c.brand = d.brand_code WHERE a.is_show = 0 AND a.del = 1  AND a.vippool_type = 1 AND a.supplier_id = ";


        // 通用类型
        String paths = "INSERT INTO  td_vipplate_upload_file (uuid,file_name,file_url,proposer_id,dept_id,insert_time,update_time,del)\n" +
                "SELECT\n" +
                "\tCONCAT('EquityApplyEquityLogo',a.id),\n" +
                "\t'权益LOGO',\n" +
                "\tc.interests_img,\n" +
                "\t322,\n" +
                "\tb.dept_id,\n" +
                "\tUNIX_TIMESTAMP()*1000,\n" +
                "\tUNIX_TIMESTAMP()*1000,\n" +
                "    0\n" +
                "FROM\n" +
                "\ttb_vipplate_interests_pool a\n" +
                "\tLEFT JOIN tb_dept_supplier b ON a.supplier_id = b.supplier_id \n" +
                "\tleft join tb_vipplate_interests c on a.interests_id = c.id \n" +
                "WHERE\n" +
                "\ta.is_show = 0 \n" +
                "\tAND a.del = 1 \n" +
                "\tAND a.vippool_type = 1\n" +
                "\tAND a.supplier_id = ";

        StringBuilder sb = new StringBuilder();
        list.forEach(e->{
            sb.append("\n")
                    .append(s2)
                    .append("'")
                    .append(e)
                    .append("' ;\t\n");
            result.add(sb.toString());
            sb.delete(0, sb.length()+1);
        });


        System.out.println(result.get(0));


        // 保存到文件
        String savePath = "C:\\Users\\23083\\Desktop\\sql\\迁移数据SQL2.sql";
        WriteStringUtil.saveResult2Txt(result,savePath);
    }
}
